#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use List::Compare;

require'/config.pl';

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});
# execute INSERT query

# ************************************* Création type_test Test open-field************************************* #

my @unite_mesure=('Durée totale en immobilité','Latence occupation zone centrale','Durée totale occupation zone centrale','Nombre de changements de zone',
'Nombre de redressements','Latence sleeping box','Durée totale sleeping box','Nombre de défécations','Nombre de sauts','Durée totale accrochage paroi');
my @id_type_test;
my $row=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description='Test open-field' AND unite_mesure LIKE ?");
foreach my $elt(@unite_mesure){
	$row->execute("%$elt%");
	while(my @unite=$row->fetchrow_array()){
		push(@id_type_test,$unite[0]);
	}
	$row->finish();
}
if(not @id_type_test){
	foreach my $elt(@unite_mesure){
		$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', 'Test open-field', '$elt')"); 
	}
}

# ************************************* Création type_test Test novel object************************************* #

my @unite_mesure2=('Latence approche nouvel objet','Fréquence approche nouvel objet','Latence de contact','Durée totale de contact',
'Durée totale occupation zone centrale','Nombre de déplacements objet');
my @id_type_test2;
my $row2=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description='Novel object' AND unite_mesure LIKE ?");
foreach my $elt(@unite_mesure2){
	$row2->execute("%$elt%");
	while(my @unite=$row2->fetchrow_array()){
		push(@id_type_test2,$unite[0]);
	}
	$row2->finish();
}
if(not @id_type_test2){	
	foreach my $elt(@unite_mesure2){
		$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', 'Novel object', '$elt')"); 
	}
}

# ************************************* Création type_test Test Pairwise discrimination************************************* #

my @unite_mesure3=('Temps','Correct touches','Trials done');
my @id_type_test3;
my $row3=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description='Pairwise discrimination' AND unite_mesure LIKE ?");
foreach my $elt(@unite_mesure3){
	$row3->execute("%$elt%");
	while(my @unite=$row3->fetchrow_array()){
		push(@id_type_test3,$unite[0]);
	}
	$row3->finish();
}
if(not @id_type_test3){	
	foreach my $elt(@unite_mesure3){
		$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', 'Pairwise discrimination', '$elt')"); 
	}
}

# ************************************* Création type_test Test Pairwise inversion************************************* #

my @unite_mesure4=('Temps','Correct touches','Trials done');
my @id_type_test4;
my $row4=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description='Pairwise inversion' AND unite_mesure LIKE ?");
foreach my $elt(@unite_mesure4){
	$row4->execute("%$elt%");
	while(my @unite=$row4->fetchrow_array()){
		push(@id_type_test4,$unite[0]);
	}
	$row4->finish();
}
if(not @id_type_test4){	
	foreach my $elt(@unite_mesure4){
		$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', 'Pairwise inversion', '$elt')"); 
	}
}

# ************************************* Création type_test questionnaire************************************* #

my @unite_mesure5=('Sest échappé','A mordu','Bouche ouverte','Vocalisations','Etat des jambes','Etat de la queue','Etat des oreilles',
'A uriné','A déféqué','Temps manipulation');
my @id_type_test5;
my $row5=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='Test comportemental' AND description='Questionnaire' AND unite_mesure LIKE ?");
foreach my $elt(@unite_mesure5){
	$row5->execute("%$elt%");
	while(my @unite=$row5->fetchrow_array()){
		push(@id_type_test5,$unite[0]);
	}
	$row5->finish();
}
if(not @id_type_test5){	
	foreach my $elt(@unite_mesure5){
		$dbh->do("INSERT INTO Type_test(label, description,unite_mesure) VALUES ('Test comportemental', 'Questionnaire', '$elt')"); 
	}
}

# ************************************* Création type_test Pesée************************************* #

my $id_type_test6=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%Pesée%' AND unite_mesure='Poids'");

if(not $id_type_test6){	
	$dbh->do("INSERT INTO Type_test(label, unite_mesure) VALUES ('Pesée', 'Poids')"); 
}

# ************************************* Création type_test Transcriptome************************************* #

my $id_type_test7=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%Transcriptome%' ");

if(not $id_type_test7){	
	$dbh->do("INSERT INTO Type_test(label) VALUES ('Transcriptome')"); 
}

# ************************************* Création type_test Séquencage arn************************************* #

my $id_type_test8=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%Séquençage ARN%' ");

if(not $id_type_test8){	
	$dbh->do("INSERT INTO Type_test(label) VALUES ('Séquençage ARN')"); 
}

# ************************************* Création type_test Test induction************************************* #

my $id_type_test9=$dbh->selectrow_array("SELECT id_type_test FROM type_test WHERE label LIKE '%Test induction%' ");

if(not $id_type_test9){	
	$dbh->do("INSERT INTO Type_test(label) VALUES ('Test induction')"); 
}

$dbh->disconnect();

